﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace HaoPeng
{
    public partial class DBset : Form
    {
        public DBset()
        {
            InitializeComponent();
            try
            {
                getDSfloor();
                getDSmachine();
                getDSattribute();
                getDSmachinecount();
                getDStable();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("读取数据出错：" + ex.Message);
            }            
        }
        //读取
        private void getDSfloor()
        {
            string sql = "select * from tree_Floor";
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql).Tables[0];
            dataGridView1.DataSource = dt;
        }
        private void getDSmachine()
        {
            string sql = "select * from tree_Machine";
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql).Tables[0];
            dataGridView2.DataSource = dt;
        }
        private void getDSattribute()
        {
            int width = 0;
            string sql = "select * from Attribute";
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql).Tables[0];
            dataGridView3.DataSource = dt;
            for (int i = 0; i < this.dataGridView3.Columns.Count; i++)
            {
                //将每一列都调整为自动适应模式
                this.dataGridView3.AutoResizeColumn(i, DataGridViewAutoSizeColumnMode.AllCells);
                //记录整个DataGridView的宽度
                width += this.dataGridView3.Columns[i].Width;
            }
            if (width > this.dataGridView3.Size.Width)
            {
                dataGridView3.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
            }
        }
        private void getDSmachinecount()
        {
            string sql = "select * from Machine";
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql).Tables[0];
            dataGridView4.DataSource = dt;
        }
        private void getDStable()
        {
            string sql = "SELECT name FROM sysobjects where type = 'U' and name like 'HP%'";
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql).Tables[0];
            dataGridView5.DataSource = dt;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            newFloor nf = new newFloor();
            nf.ShowDialog();
            if (nf.DialogResult == DialogResult.OK)
            {
                getDSfloor();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            try
            {
                string activeRow = dataGridView1.CurrentRow.Cells[0].Value.ToString();
                string sql = "delete from tree_Floor where ID=@ID";
                MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
                DialogResult dr = MessageBox.Show("确定要删除该数据吗?", "删除数据表", messButton);
                if (dr == DialogResult.OK)
                {
                    int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, new SqlParameter("@ID", Convert.ToInt32(activeRow)));
                    MessageBox.Show("共删除了" + result + "条数据！");
                    getDSfloor();
                }
                else
                {

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("删除楼层数据失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                //获取选中行的索引
                int numHang = this.dataGridView1.CurrentRow.Index;
                //获取选中行 中 列的索引
                int numLie = this.dataGridView1.CurrentCell.ColumnIndex;
                //获取选中列的Name
                string LieName = this.dataGridView1.Columns[numLie].Name;
                //获得指定行列修改过后值
                string strInfo = this.dataGridView1.Rows[numHang].Cells[numLie].Value.ToString();
                //获得修改行的主键
                int ID = int.Parse(this.dataGridView1.Rows[numHang].Cells["ID"].Value.ToString());
                //string sql = "update tree_Floor set @LieName = @strInfo where ID = @ID";
                string sql = string.Format("update tree_Floor set {0} = '{1}' where ID = {2}", LieName, strInfo, ID);
                int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql);
                MessageBox.Show("更新成功！");
                dataGridView1.Refresh();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("更新楼层表失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            newMachineType nm = new newMachineType();
            nm.ShowDialog();
            if (nm.DialogResult == DialogResult.OK)
            {
                getDSmachine();
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {            
            try
            {
                string activeRow = dataGridView2.CurrentRow.Cells[0].Value.ToString();
                string sql = "delete from tree_Machine where ID=@ID";
                MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
                DialogResult dr = MessageBox.Show("确定要删除该数据吗?", "删除数据表", messButton);
                if (dr == DialogResult.OK)
                {
                    int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, new SqlParameter("@ID", Convert.ToInt32(activeRow)));
                    MessageBox.Show("共删除了" + result + "条数据！");
                    getDSmachine();
                }
                else
                {

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("删除设备类型数据失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView2_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                //获取选中行的索引
                int numHang = this.dataGridView2.CurrentRow.Index;
                //获取选中行 中 列的索引
                int numLie = this.dataGridView2.CurrentCell.ColumnIndex;
                //获取选中列的Name
                string LieName = this.dataGridView2.Columns[numLie].Name;
                //获得指定行列修改过后值
                string strInfo = this.dataGridView2.Rows[numHang].Cells[numLie].Value.ToString();
                //获得修改行的主键
                int ID = int.Parse(this.dataGridView2.Rows[numHang].Cells["ID"].Value.ToString());
                //string sql = "update tree_Machine set @LieName = @strInfo where ID = @ID";
                string sql = string.Format("update tree_Machine set {0} = '{1}' where ID = {2}", LieName, strInfo, ID);
                int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql);
                MessageBox.Show("更新成功！");
                dataGridView2.Refresh();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("更新设备类型表失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void button9_Click(object sender, EventArgs e)
        {
            newAttribute nm = new newAttribute();
            nm.ShowDialog();
            if (nm.DialogResult == DialogResult.OK)
            {
                getDSattribute();
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {            
            try
            {
                string activeRow = dataGridView3.CurrentRow.Cells[0].Value.ToString();
                string sql = "delete from Attribute where ID=@ID";
                MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
                DialogResult dr = MessageBox.Show("确定要删除该数据吗?", "删除数据表", messButton);
                if (dr == DialogResult.OK)
                {
                    int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, new SqlParameter("@ID", Convert.ToInt32(activeRow)));
                    MessageBox.Show("共删除了" + result + "条数据！");
                    getDSattribute();
                }
                else
                {

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("删除设备类型参数数据失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView3_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {            
            try
            {
                //获取选中行的索引
                int numHang = this.dataGridView3.CurrentRow.Index;
                //获取选中行 中 列的索引
                int numLie = this.dataGridView3.CurrentCell.ColumnIndex;
                //获取选中列的Name
                string LieName = this.dataGridView3.Columns[numLie].Name;
                //获得指定行列修改过后值
                string strInfo = this.dataGridView3.Rows[numHang].Cells[numLie].Value.ToString();
                //获得修改行的主键
                int ID = int.Parse(this.dataGridView3.Rows[numHang].Cells["ID"].Value.ToString());
                //string sql = "update Attribute set @LieName = @strInfo where ID = @ID";
                string sql = string.Format("update Attribute set {0} = '{1}' where ID = {2}", LieName, strInfo, ID);
                int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql);
                MessageBox.Show("更新成功！");
                dataGridView3.Refresh();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("更新设备类型参数表失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void button12_Click(object sender, EventArgs e)
        {
            newMachine nm = new newMachine();
            nm.ShowDialog();
            if (nm.DialogResult == DialogResult.OK)
            {
                getDSmachinecount();
            }
        }

        private void button10_Click(object sender, EventArgs e)
        {
            try
            {
                string activeRow = dataGridView4.CurrentRow.Cells[0].Value.ToString();
                string sql = "delete from Machine where MachineName=@MachineName";
                MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
                DialogResult dr = MessageBox.Show("确定要删除该数据吗?", "删除数据表", messButton);
                if (dr == DialogResult.OK)
                {
                    int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql, new SqlParameter("@MachineName", activeRow));
                    MessageBox.Show("共删除了" + result + "条数据！");
                    getDSmachinecount();
                }
                else
                {

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("删除设备数据失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void dataGridView4_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                //获取选中行的索引
                int numHang = this.dataGridView4.CurrentRow.Index;
                //获取选中行 中 列的索引
                int numLie = this.dataGridView4.CurrentCell.ColumnIndex;
                //获取选中列的Name
                string LieName = this.dataGridView4.Columns[numLie].Name;
                //获得指定行列修改过后值
                string strInfo = this.dataGridView4.Rows[numHang].Cells[numLie].Value.ToString();
                //获得修改行的主键
                string MachineName = this.dataGridView4.Rows[numHang].Cells["MachineName"].Value.ToString();
                //string sql = "update Machine set @LieName = @strInfo where MachineName = @MachineName";
                string sql = string.Format("update Machine set {0} = '{1}' where MachineName = '{2}'", LieName, strInfo, MachineName);
                int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql);
                MessageBox.Show("更新成功！");
                dataGridView4.Refresh();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("更新设备表失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void button13_Click(object sender, EventArgs e)
        {
            try
            {
                string tableName = textBox5.Text;
                string sql = string.Format(
                                            " CREATE TABLE " + tableName + "" +
                                            " (" +
                                            "    DeviceName VARCHAR(20)," +
                                            "    Data VARCHAR(255)," +
                                            "    Datetime datetime" +
                                            ")"
                );
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql);
                MessageBox.Show("添加成功！");
                getDStable();
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("新增数据表失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }

        private void button14_Click(object sender, EventArgs e)
        {
            try
            {
                string activeRow = dataGridView5.CurrentRow.Cells[0].Value.ToString();
                string sql = "drop table " + activeRow;
                MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
                DialogResult dr = MessageBox.Show("确定要删除" + activeRow + "吗?", "删除数据表", messButton);
                if (dr == DialogResult.OK)
                {
                    int result = SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, sql);
                    MessageBox.Show("删除成功！");
                    getDStable();
                }
                else
                {

                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("删除数据表失败：" + ex.Message);
                MessageBox.Show(ex.Message);
            }
        }
    }
}
